<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    
<meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>


<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />

<meta name="theme-color" content="#f8f5ec" />
<meta name="msapplication-navbutton-color" content="#f8f5ec">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="#f8f5ec">



  <meta name="description" content="SQLAlchemy对数据基本操作"/>




  <meta name="keywords" content="开发, flask, SQLAlchemy, 八一" />



  <meta name="baidu-site-verification" content="HhUstaSjr0" />



  <meta name="google-site-verification" content="UA-102975942-1" />






  <link rel="alternate" href="/atom.xml" title="八一">




  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=2.6.0" />



<link rel="canonical" href="https://bay1.top/2017/08/07/SQLAIchemy对数据基本操作/"/>


<link rel="stylesheet" type="text/css" href="/css/style.css?v=2.6.0" />
<link rel="stylesheet" type="text/css" href="/css/prettify.css" media="screen" />
<link rel="stylesheet" type="text/css" href="/css/sons-of-obsidian.css" media="screen" />



  <link rel="stylesheet" type="text/css" href="/lib/fancybox/jquery.fancybox.css" />




  
  <script id="baidu_analytics">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?9a885cc9fb6cd7bcef579deb8efe8a70";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>



  <script id="google_analytics">
    (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
        (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
        m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
        })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

        ga('create', 'UA-102975942-1', 'auto');
        ga('send', 'pageview');
  </script>










    <title> SQLAlchemy对数据基本操作 - 八一 </title>
  </head>

  <body><div id="mobile-navbar" class="mobile-navbar">
  <div class="mobile-header-logo">
    <a href="/." class="logo">八一</a>
  </div>
  <div class="mobile-navbar-icon">
    <span></span>
    <span></span>
    <span></span>
  </div>
</div>

<nav id="mobile-menu" class="mobile-menu slideout-menu">
  <ul class="mobile-menu-list">
    
      <a href="/archives">
        <li class="mobile-menu-item">
          
          
            文章
          
        </li>
      </a>
    
      <a href="/tags">
        <li class="mobile-menu-item">
          
          
            标签
          
        </li>
      </a>
    
      <a href="/about">
        <li class="mobile-menu-item">
          
          
            关于/友链
          
        </li>
      </a>
    
      <a href="/search">
        <li class="mobile-menu-item">
          
          
            站内搜索
          
        </li>
      </a>
    
  </ul>
</nav>

    <div class="container" id="mobile-panel">
      <header id="header" class="header"><div class="logo-wrapper">
  <a href="/." class="logo">八一</a>
</div>

<nav class="site-navbar">
  
    <ul id="menu" class="menu">
      
        <li class="menu-item">
          <a class="menu-item-link" href="/archives">
            
            
              文章
            
          </a>
        </li>
      
        <li class="menu-item">
          <a class="menu-item-link" href="/tags">
            
            
              标签
            
          </a>
        </li>
      
        <li class="menu-item">
          <a class="menu-item-link" href="/about">
            
            
              关于/友链
            
          </a>
        </li>
      
        <li class="menu-item">
          <a class="menu-item-link" href="/search">
            
            
              站内搜索
            
          </a>
        </li>
      
    </ul>
  
</nav>

      </header>

      <main id="main" class="main">
        <div class="content-wrapper">
          <div id="content" class="content">
            
  
  <article class="post">
    <header class="post-header">
      <h1 class="post-title">
        
          SQLAlchemy对数据基本操作
        
      </h1>

      <div class="post-meta">
        <span class="post-time">
          2017-08-07
        </span>
        
        
        
      </div>
    </header>

    
    
  <div class="post-toc" id="post-toc">
    <h2 class="post-toc-title">文章目录</h2>
    <div class="post-toc-content">
      <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#增加数据"><span class="toc-text">增加数据</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#读取数据"><span class="toc-text">读取数据</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#修改数据"><span class="toc-text">修改数据</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#删除数据"><span class="toc-text">删除数据</span></a></li></ol>
    </div>
  </div>


    <div class="post-content">
      
        <p><strong>先前在这篇文章有些创建操作介绍 <a href="https://bayi87.github.io/2017/05/19/Mysql%E5%AE%89%E8%A3%85%E5%92%8C%E6%93%8D%E4%BD%9C-flask/" target="_blank" rel="noopener">Mysql安装和操作(flask)</a></strong> <a id="more"></a></p>
<p><strong>以下为读《深入理解Flask》</strong></p>
<blockquote>
<p>对各种数据储存策略有四种基本功能类型：添加,读取,修改,删除</p>
</blockquote>
<h2 id="增加数据"><a href="#增加数据" class="headerlink" title="增加数据"></a>增加数据</h2><p><img src="https://s1.ax1x.com/2018/01/01/pSRnXR.png" alt="增加数据"></p>
<h2 id="读取数据"><a href="#读取数据" class="headerlink" title="读取数据"></a>读取数据</h2><blockquote>
<p>读取数据也许是操作种类比较复杂的功能类型</p>
</blockquote>
<p>一个简单的例子</p>
<figure class="highlight fsharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">&gt;&gt;&gt; users=User.query.all()</span><br><span class="line">&gt;&gt;&gt; users</span><br><span class="line"><span class="meta">[&lt;User 'test'&gt;]</span></span><br></pre></td></tr></table></figure>
<p>一些常用语句(可以链式调用)<br><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">User.query.all <span class="comment">##查询User模型在数据库中所有行</span></span><br><span class="line">User.query.h2mit(<span class="number">5</span>).all <span class="comment">##指定行数返回</span></span><br><span class="line">User.query.first() <span class="comment">##返回一行数据</span></span><br><span class="line">User.query.filter(User.id == <span class="number">2</span>).first().name</span><br><span class="line">User.query.get(<span class="number">2</span>).name <span class="comment">##以主键获取,等效于上句</span></span><br><span class="line">Post.query.paginate(<span class="number">2</span>,<span class="number">10</span>) <span class="comment">##返回2-10的对象,这个设计为了分页</span></span><br><span class="line">User.query.filter_by(username=<span class="string">"test"</span>).all() <span class="comment">##精确过滤</span></span><br><span class="line">User.query.filter(User.id&gt;<span class="number">1</span>).all() <span class="comment">##user.id大于1的</span></span><br><span class="line"><span class="comment">##filter参数接受任何python表达式</span></span><br><span class="line">User.query.order_by(User.username).all() <span class="comment">##正向排序</span></span><br><span class="line">User.query.order_by(User.username.desc()).all() <span class="comment">##反向排序</span></span><br><span class="line"><span class="comment">##order_by控制排序方式</span></span><br></pre></td></tr></table></figure></p>
<p><img src="https://s1.ax1x.com/2018/01/01/pSRKn1.png" alt="orderby"></p>
<p>paginate-Add：假如命令page=Post.query.paginate(2,10)<br><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">page.items <span class="comment">##返回这一页包含数据</span></span><br><span class="line">page.page <span class="comment">##页数</span></span><br><span class="line">page.pages <span class="comment">##总页数</span></span><br><span class="line">page.has_prev OR page.has_next <span class="comment">##上一页或者下一页是否有数据</span></span><br></pre></td></tr></table></figure></p>
<p>filter-Add: 一些复杂的SQL语句也可以转化<br><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">User.query.filter(</span><br><span class="line">		User.username.in_([<span class="string">'test'</span>,<span class="string">'test1'</span>]),</span><br><span class="line">		User.password_hash==None</span><br><span class="line">	).first()</span><br><span class="line">User.query.filter(</span><br><span class="line">		not_(User.password_hash==None)</span><br><span class="line">	).first() <span class="comment">##拥有密码的user</span></span><br><span class="line">User.query.filter(</span><br><span class="line">		or_(not_(User.password_hash==None),User.id&gt;1)</span><br><span class="line">	).first() <span class="comment">##组合调用</span></span><br></pre></td></tr></table></figure></p>
<blockquote>
<p>SQLAlchemy中与None的比较会被当成与NULL比较</p>
</blockquote>
<h2 id="修改数据"><a href="#修改数据" class="headerlink" title="修改数据"></a>修改数据</h2><blockquote>
<p>调用update方法更新数据</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">&gt;&gt;&gt; User.query.filter_by(username=<span class="string">"test"</span>).update(&#123;</span><br><span class="line">	<span class="string">'password_hash'</span>:<span class="string">'test'</span></span><br><span class="line">	&#125;)</span><br><span class="line">1</span><br><span class="line">&gt;&gt;&gt; db.session.commit()</span><br></pre></td></tr></table></figure>
<h2 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">&gt;&gt;&gt; user=User.query.filter_by(username=<span class="string">"test"</span>).first()</span><br><span class="line">&gt;&gt;&gt; db.session.delete(user)</span><br><span class="line">&gt;&gt;&gt; db.session.commit()</span><br></pre></td></tr></table></figure>
      
    </div>

    
      
      



      
      
    

    
      <footer class="post-footer">
        
          <div class="post-tags">
            
              <a href="/tags/开发/">开发</a>
            
              <a href="/tags/flask/">flask</a>
            
              <a href="/tags/SQLAlchemy/">SQLAlchemy</a>
            
          </div>
        
        
        
  <nav class="post-nav">
    
      <a class="prev" href="/2017/08/09/SQLAIchemy数据模型关联/">
        <i class="iconfont icon-left"></i>
        <span class="prev-text nav-default">SQLAIchemy数据模型关联</span>
        <span class="prev-text nav-mobile">上一篇</span>
      </a>
    
    
      <a class="next" href="/2017/08/07/如何在github上pull-request代码/">
        <span class="next-text nav-default">如何在github上pull request代码</span>
        <span class="prev-text nav-mobile">下一篇</span>
        <i class="iconfont icon-right"></i>
      </a>
    
  </nav>

      </footer>
    

  </article>


          </div>
          
  <div class="comments" id="comments">
      <div id="disqus_thread">
        <noscript>
          Please enable JavaScript to view the
          <a href="//disqus.com/?ref_noscript">comments powered by Disqus.</a>
        </noscript>
      </div> 
    </div>
  </div>


        </div>
      </main>

      <footer id="footer" class="footer">

  <div class="social-links">
    
      
        
          <a href="https://github.com/bay1" class="iconfont icon-github" title="github"></a>
        
      
    
      
        
          <a href="http://weibo.com/3190704711/profile?topnav=1&wvr=6&is_all=1" class="iconfont icon-weibo" title="weibo"></a>
        
      
    
      
    
      
    
      
    
    
    
  </div>


<div class="copyright">
  <span class="copyright-year">
    
    &copy; 
     
      2016 - 
    
    2018
    <span class="author">bay1</span>
  </span>
</div>
      </footer>

      <div class="back-to-top" id="back-to-top">
        <i class="iconfont icon-up"></i>
      </div>
    </div>

    
  
  <script type="text/javascript">
    var disqus_config = function () {
        this.page.url = 'https://bay1.top/2017/08/07/SQLAIchemy对数据基本操作/';
        this.page.identifier = '2017/08/07/SQLAIchemy对数据基本操作/';
        this.page.title = 'SQLAlchemy对数据基本操作';
    };
    (function() {
    var d = document, s = d.createElement('script');

    s.src = '//https-blog-flywinky-top-1.disqus.com/embed.js';

    s.setAttribute('data-timestamp', +new Date());
    (d.head || d.body).appendChild(s);
    })();  
  </script>



    
  





  
    <script type="text/javascript" src="/lib/jquery/jquery-3.1.1.min.js"></script>
  

  
    <script type="text/javascript" src="/lib/slideout/slideout.js"></script>
  

  
    <script type="text/javascript" src="/lib/fancybox/jquery.fancybox.pack.js"></script>
  


    <script type="text/javascript" src="/js/src/even.js?v=2.6.0"></script>
<script type="text/javascript" src="/js/src/bootstrap.js?v=2.6.0"></script>
<script src="/js/prettify.js"></script>
<script type="text/javascript">
$(document).ready(function(){
 $('pre').addClass('prettyprint');
   prettyPrint();
 })
</script>
  </body>
</html>
